package cn.micro.dc_excel.controller;

import cn.micro.dc_excel.tools.ToolsDaochu;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@RestController
public class ExportController {

    /*注册账单*/
    @RequestMapping("/loginOrder")
    public List login(HttpServletRequest req, HttpServletResponse res, String start, String end, String applyTime, String endTime, String username, String tell) throws Exception {
        String sql = " SELECT a.*,d.* FROM (SELECT b.* FROM member_order b WHERE ext1='1') a LEFT JOIN (SELECT c.* FROM tf_founderapplyfor c GROUP BY c.applyfor_user_id) d ON a.member_id=d.applyfor_user_id where 1=1";
        if (!StringUtils.isBlank(start)) {
            sql += " and create_date >= " + start + "";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and create_date <= " + end + "";
        }
        if (!StringUtils.isBlank(username)) {
            sql += " and true_name = " + username + "";
        }
        if (!StringUtils.isBlank(tell)) {
            sql += " and mobile = " + tell + "";
        }
        sql += "ORDER BY create_date DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "注册时间");
        map.put("0-2", "申请时间");
        map.put("0-3", "终止时间");
        map.put("0-4", "账号");
        map.put("0-5", "姓名");
        map.put("0-6", "身份证号");
        map.put("0-7", "手机号");
        map.put("0-8", "订单编号");
        map.put("0-9", "会员实缴");
        map.put("0-10", "创客实缴");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i + 1);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("create_date"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("applyfor_date"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("due_date"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("applyfor_user"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("identity_number"));
            map.put("" + (i + 1) + "-7", ((Map) list.get(i)).get("mobile"));
            map.put("" + (i + 1) + "-8", ((Map) list.get(i)).get("order_sn"));
            if ((((Map) list.get(i)).get("ext3")).equals("199.0")) {
                map.put("" + (i + 1) + "-9", ((Map) list.get(i)).get("ext3"));
                map.put("" + (i + 1) + "-10", ((Map) list.get(i)).get(""));
            } else {
                map.put("" + (i + 1) + "-9", ((Map) list.get(i)).get(""));
                map.put("" + (i + 1) + "-10", ((Map) list.get(i)).get("ext3"));
            }
        }
        long ts = System.currentTimeMillis(); 
        String table = "注册账单&"+String.valueOf(ts);
        ToolsDaochu.getTfAttenceListExport(req, res, table, map);
        return list;

    }

    /*联盟账单*/
    @RequestMapping("/unionOrder")
    public void union(HttpServletRequest req, HttpServletResponse res, String start, String end, String mode_payment, String login_name, String shop_name, String member_name, String order_sn) throws Exception {
        String sql = "SELECT b.*,c.shop_name,u.login_name,d.*"
					+" FROM (SELECT a.* FROM tf_order a WHERE a.order_status = '1') b"
					+" LEFT JOIN tf_shop c ON b.si_id=c.shop_id"
					+" LEFT JOIN USER u ON b.si_id=u.id LEFT JOIN tf_order_share d ON b.`order_id`=d.`order_id` where 1=1";
        if (!StringUtils.isBlank(start)) {
            sql += " and pay_date >= " + start + "";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and pay_date <= " + end + "";
        }
        if (!StringUtils.isBlank(mode_payment)) {
            sql += "and mode_payment =" + mode_payment + "";
        }
        if (!StringUtils.isBlank(login_name)) {
            sql += "and login_name =" + login_name + "";
        }
        if (!StringUtils.isBlank(shop_name)) {
            sql += "and tf_shop.`shop_name` =" + shop_name + "";
        }
        if (!StringUtils.isBlank(member_name)) {
            sql += "and member_name =" + member_name + "";
        }
        if (!StringUtils.isBlank(order_sn)) {
            sql += " and order_sn = " + order_sn + "";
        }
        sql +="order by pay_date DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "交易时间");
        map.put("0-2", "订单编号");
        map.put("0-3", "商家名称");
        map.put("0-4", "返佣比例");
        map.put("0-5", "商家账号");
        map.put("0-6", "用户名");
        map.put("0-7", "支付类型");
        map.put("0-8", "消费额");
        map.put("0-9", "商家（收入）");
        map.put("0-10", "总部（收入）");
        map.put("0-11", "基金");
        map.put("0-12", "业务");
        map.put("0-13", "市级代理");
        map.put("0-14", "区级代理");
        /*SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm");*/
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i + 1);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("pay_date"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("order_sn"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("shop_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("shop_ratio"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("member_name"));
            map.put("" + (i + 1) + "-7", ((Map) list.get(i)).get("mode_payment"));
            map.put("" + (i + 1) + "-8", ((Map) list.get(i)).get("order_money"));
            map.put("" + (i + 1) + "-9", ((Map) list.get(i)).get("shop_share"));
            map.put("" + (i + 1) + "-10", ((Map) list.get(i)).get("zb_share"));
            map.put("" + (i + 1) + "-11", ((Map) list.get(i)).get("jj_share"));
            map.put("" + (i + 1) + "-12", ((Map) list.get(i)).get("agency_share"));
            map.put("" + (i + 1) + "-13", ((Map) list.get(i)).get("city_share"));
            map.put("" + (i + 1) + "-14", ((Map) list.get(i)).get("district_share"));
           /* String fromDate = simpleFormat.format(simpleFormat.parse((String) ((Map)list.get(i)).get("order_createtime")));
            String toDate = simpleFormat.format(new Date());
            long from = simpleFormat.parse(fromDate).getTime();
            long to = simpleFormat.parse(toDate).getTime();
            int days = (int) ((to - from)/(1000 * 60 * 60 * 24));
            map.put(""+(i+1)+"-3",days);*/
        }
        long ts = System.currentTimeMillis(); 
        String table = "联盟账单&"+String.valueOf(ts);
        ToolsDaochu.getTfAttenceListExport(req, res, table, map);

    }

    /*积分账单*/
    @RequestMapping("/integralOrder")
    public void integral(HttpServletRequest req, HttpServletResponse res, String start, String end, String type, String sn, String realname, String username) throws Exception {
        String sql = " SELECT a.*,m.login_name,b.mobile,b.true_name as name FROM tf_integral_detail a"
			        +" LEFT JOIN (SELECT c.mobile,c.applyfor_user_id,c.true_name FROM tf_founderapplyfor c GROUP BY c.applyfor_user_id) b"
			        +" ON a.member_id=b.applyfor_user_id LEFT JOIN member m ON a.member_id=m.id"
			        +" WHERE 1=1";
        if (!StringUtils.isBlank(start)) {
            sql += " and a.itgd_time >= " + start + "";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and a.itgd_time <= " + end + "";
        }
        if (!StringUtils.isBlank(type)) {
            sql += " a.and itgd_type = " + type + "";
        }
        if (!StringUtils.isBlank(sn)) {
            sql += " a.and itgd_order = " + sn + "";
        }
        if (!StringUtils.isBlank(realname)) {
            sql += " and name = " + realname + "";
        }
        if (!StringUtils.isBlank(username)) {
            sql += " and m.login_name = " + username + "";
        }
        sql += " order by a.itgd_time DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "交易时间");
        map.put("0-2", "订单编号");
        map.put("0-3", "用户名");
        map.put("0-4", "真实姓名");
        map.put("0-5", "手机号");
        map.put("0-6", "积分收支类型");
        map.put("0-7", "积分数");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i + 1);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("itgd_time"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("itgd_order"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("name"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("mobile"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("itgd_type"));
            map.put("" + (i + 1) + "-7", ((Map) list.get(i)).get("itgd_num"));

        }
        long ts = System.currentTimeMillis(); 
        String table = "积分账单&"+String.valueOf(ts);
        ToolsDaochu.getTfAttenceListExport(req, res, table, map);
    }

    /*金币账单*/
    @RequestMapping("/goldOrder")
    public void goldOrder(HttpServletRequest req, HttpServletResponse res, String start, String end, String gold_type, String realname, String sn, String username) throws Exception {
        String sql = " SELECT a.*,m.login_name,b.mobile,b.true_name FROM tf_gold_detail a"
			        +" LEFT JOIN (SELECT c.mobile,c.applyfor_user_id,c.true_name"
			        +" FROM tf_founderapplyfor c GROUP BY c.applyfor_user_id) b"
			        +" ON a.member_id=b.applyfor_user_id LEFT JOIN member m ON a.member_id=m.id where 1=1";
        if (!StringUtils.isBlank(start)) {
            sql += " and a.gd_time >= " + start + "";
        }
        if (!StringUtils.isBlank(end)) {
            sql += " and a.gd_time <= " + end + "";
        }
        if (!StringUtils.isBlank(gold_type)) {
            sql += " and a.gd_type = " + gold_type + "";
        }
        if (!StringUtils.isBlank(sn)) {
            sql += " and a.gd_order = " + sn + "";
        }
        if (!StringUtils.isBlank(realname)) {
            sql += " and b.true_name = " + realname + "";
        }
        if (!StringUtils.isBlank(username)) {
            sql += " and m.login_name = " + username + "";
        }
        sql += " order by a.gd_time DESC";
        List list = daochu(sql);
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "交易时间");
        map.put("0-2", "订单编号");
        map.put("0-3", "用户名");
        map.put("0-4", "真实姓名");
        map.put("0-5", "手机号");
        map.put("0-6", "金币收支类型");
        map.put("0-7", "金币数");
        for (int i = 0; i < list.size(); i++) {
            map.put("" + (i + 1) + "-0", i + 1);
            map.put("" + (i + 1) + "-1", ((Map) list.get(i)).get("gd_time"));
            map.put("" + (i + 1) + "-2", ((Map) list.get(i)).get("gd_order"));
            map.put("" + (i + 1) + "-3", ((Map) list.get(i)).get("login_name"));
            map.put("" + (i + 1) + "-4", ((Map) list.get(i)).get("true_name"));
            map.put("" + (i + 1) + "-5", ((Map) list.get(i)).get("mobile"));
            map.put("" + (i + 1) + "-6", ((Map) list.get(i)).get("gd_type"));
            map.put("" + (i + 1) + "-7", ((Map) list.get(i)).get("gd_goldnum"));

        }
        long ts = System.currentTimeMillis(); 
        String table = "金币账单&"+String.valueOf(ts);
        ToolsDaochu.getTfAttenceListExport(req, res, table, map);
    }

    /*仁和币账单*/
    @RequestMapping("/renheOrder")
    public void renheOrder(HttpServletRequest req, HttpServletResponse res, String start, String end, String type, String sn, String username) throws Exception {
        /*导出user的总部、基金和商家的信息*/
        String sql1 = "SELECT a.*,b.login_name,b.name,c.local_name as lname,c.parent_id FROM tf_coin_variation a"
			        +" LEFT JOIN USER b ON a.rh_object=b.id"
			        +" LEFT JOIN regions c  ON a.rh_object=c.region_id  where 1=1";
        if (!StringUtils.isBlank(start)) {
            sql1 += " and a.rh_crawlTime >= " + start + "";
        }
        if (!StringUtils.isBlank(end)) {
            sql1 += " and a.rh_crawlTime <= " + end + "";
        }
        if (!StringUtils.isBlank(type)) {
            sql1 += " and a.rh_crawExplain = " + type + "";
        }
        if (!StringUtils.isBlank(sn)) {
            sql1 += " and a.rh_orderId = " + sn + "";
        }
        if (!StringUtils.isBlank(username)) {
            sql1 += " and applyfor_user = " + username + "";
        }
        List list1 = daochu(sql1);
        System.out.println(list1.size());
        HashMap<String, Object> map = new HashMap<>();
        map.put("0-0", "序号");
        map.put("0-1", "交易时间");
        map.put("0-2", "订单编号");
        map.put("0-3", "真实姓名/商家名称");
        map.put("0-4", "个人账号/商家账号");
        map.put("0-5", "仁和币收支类型");
        map.put("0-6", "仁和币数");
        String regex = "^[0-9\u4e00-\u9fa5]+$";//只含有数字
        String regex2 = "^[a-z0-9A-Z\u4e00-\u9fa5]+$";//有数字或者有字母*/
        for (int i = 0; i < list1.size(); i++) {
        	String rh_type=(String) ((Map) list1.get(i)).get("rh_type");
        	String rh_object = (String) ((Map) list1.get(i)).get("rh_object");
	            map.put("" + (i + 1) + "-0", i + 1);
	            map.put("" + (i + 1) + "-1", ((Map) list1.get(i)).get("rh_crawlTime"));
	            map.put("" + (i + 1) + "-2", ((Map) list1.get(i)).get("rh_orderId"));
	            //String regex = "^[0-9\u4e00-\u9fa5]+$";//只含有数字
	            //String regex = "^[a-zA-Z\u4e00-\u9fa5]+$";//只含有字母
	            /*String regex = "^[a-z0-9A-Z\u4e00-\u9fa5]+$";//有数字或者有字母*/
	           /* if(((String) ((Map) list1.get(i)).get("rh_object")).matches(regex)){
	            	System.out.println("111111");
	            	System.out.println(((Map) list1.get(i)).get("rh_object"));
	            }else{
	            	System.out.println("000000");
	            	System.out.println(((Map) list1.get(i)).get("rh_object"));
	            }*/
            if(rh_type.equals("user") && rh_object.equals("zb")){//总部
	        	map.put("" + (i + 1) + "-3", ("总部"));
	            map.put("" + (i + 1) + "-4", ("总部"));
            }else if(rh_type.equals("user") && rh_object.equals("jj")){//基金
            	map.put("" + (i + 1) + "-3", ("基金"));
	            map.put("" + (i + 1) + "-4", ("基金"));
            }else if(rh_type.equals("user") && !rh_object.equals("jj") && !rh_object.equals("zb")){
            	if(rh_object.matches(regex)){//地区----只含有数字
            		String sql2 = "SELECT a.local_name as rname FROM regions a where 1=1";
            		if (!StringUtils.isBlank(rh_object)) {
                        sql2 += " and a.region_id = " + rh_object + "";
                    }
                	List list2=daochu(sql2);
                	if(list2.size()>0){
                		map.put("" + (i + 1) + "-3", ((Map) list2.get(0)).get("rname"));
                	}else{
                		map.put("" + (i + 1) + "-3", (""));
                	}
     	            map.put("" + (i + 1) + "-4", ((Map) list1.get(i)).get("lname"));
     	           System.out.println("regions："+rh_object);
            	}else{//商家
            		map.put("" + (i + 1) + "-3", ((Map) list1.get(i)).get("name"));
     	            map.put("" + (i + 1) + "-4", ((Map) list1.get(i)).get("login_name"));
     	            System.out.println("shop："+rh_object);
            	}
            }else if(rh_type.equals("member")){//member
            	String sql3 = "SELECT d.login_name,e.name FROM member d JOIN member_order e ON d.id=e.member_id WHERE e.ext1='1'";
            	if (!StringUtils.isBlank(rh_object)) {
                    sql3 += " and d.id = '" + rh_object+"'";
                }
            	List list3=daochu(sql3);
            	if(list3.size()>0){
            		System.out.println("member："+rh_object);
            		map.put("" + (i + 1) + "-3", ((Map) list3.get(0)).get("name"));
     	            map.put("" + (i + 1) + "-4", ((Map) list3.get(0)).get("login_name"));
            	}else{
            		System.out.println("member242342："+rh_object);
            		map.put("" + (i + 1) + "-3", (""));
     	            map.put("" + (i + 1) + "-4", (""));
            	}
            }
	            map.put("" + (i + 1) + "-5", ((Map) list1.get(i)).get("rh_crawExplain"));
	            map.put("" + (i + 1) + "-6", ((Map) list1.get(i)).get("rh_crawlNum"));

        }
        long ts = System.currentTimeMillis(); 
        String table = "仁和币账单&"+String.valueOf(ts);
        ToolsDaochu.getTfAttenceListExport(req, res, table, map);
    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List daochu(String sql) {
    	System.out.println("=sql="+sql);
        List list = jdbcTemplate.queryForList(sql);
        System.out.println("==="+list);
        /*System.out.println(list.size());*/
        return list;

    }

}
